﻿using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace RestService.Processing
{
    public class GroupAP : DoiTuong
    {

        #region int AID property
        public int AID
        {
            get
            {
                if (_DataRow["AID"] is int) return (int)_DataRow["AID"];
                return 0;
            }
            set
            {
                _DataRow["AID"] = value;
            }
        }
        #endregion

        #region int PID property
        public int PID
        {
            get
            {
                if (_DataRow["PID"] is int) return (int)_DataRow["PID"];
                return 0;
            }
            set
            {
                _DataRow["PID"] = value;
            }
        }
        #endregion

        #region int RSS property
        public int RSS
        {
            get
            {
                if (_DataRow["RSS"] is int) return (int)_DataRow["RSS"];
                return 0;
            }
            set
            {
                _DataRow["RSS"] = value;
            }
        }
        #endregion

        public GroupAP(DataRow dataRow)
            : base(dataRow)
        {
            DanhMucGroupAP dm = new DanhMucGroupAP();
            //if (this.ID <= 0) this.ID = dm.GetNextID();
        }
    }

    public class DanhMucGroupAP : DanhMucDoiTuong, IEnumerable
    {
        #region Instance property: Singleton Design pattern
        private static DanhMucGroupAP _Instance;
        public static DanhMucGroupAP Instance
        {
            get
            {
                if (_Instance == null)
                {
                    _Instance = new DanhMucGroupAP();
                    _Instance.DataView = _Instance.DataTable.DefaultView;
                }
                else _Instance.LoadFromDB();

                return _Instance;
            }
        }
        #endregion

        #region override TableName property
        public override string TableName
        {
            get
            {
                return "Signal";
            }
        }
        #endregion

        #region Methods required by IEnumerable (use for foreach)
        public IEnumerator GetEnumerator()
        {
            return new TokenEnumerator(this);
        }

        private class TokenEnumerator : IEnumerator
        {
            private int _Position = -1;
            DanhMucDoiTuong _Dstk;

            public TokenEnumerator(DanhMucDoiTuong dstk)
            {
                _Dstk = dstk;
            }

            // Declare the MoveNext method required by IEnumerator:
            public bool MoveNext()
            {
                if (_Position < _Dstk.DataView.Count - 1)
                {
                    _Position++;
                    return true;
                }
                else
                {
                    return false;
                }
            }

            // Declare the Reset method required by IEnumerator:
            public void Reset()
            {
                _Position = -1;
            }

            // Declare the Current property required by IEnumerator:
            public object Current
            {
                get
                {
                    return new GroupAP((_Dstk.DataView[_Position].Row));
                }
            }
        }
        #endregion

        public GroupAP this[int index]
        {
            get
            {
                if (0 <= index && index <= this.Count)
                {
                    return new GroupAP(DataTable.Rows[index]);
                }
                else
                {
                    return null;
                }
            }
        }

        public GroupAP NewDoiTuong()
        {
            return new GroupAP(DataTable.NewRow());
        }

        public GroupAP GetByProperty(string property, object value)
        {
            DataRow[] rows = GetDataRows(property, value);
            if (rows.Length > 0) return new GroupAP(rows[0]);
            else return null;
        }

        public GroupAP GetByID(Guid objID)
        {
            return GetByProperty("ID", objID);
        }

        public GroupAP GetByToaDoXY(decimal X, decimal Y)
        {
            DanhMucGroupAP dm = Filter(string.Format("ToaDoX = {0} AND ToaDoY = {1}", X, Y));
            if (dm.DataView != null && dm.DataView.Count == 1)
                return new GroupAP(dm.DataView[0].Row);
            else return null;
        }

        public static DanhMucGroupAP GetAll()
        {
            return Instance;
        }

        public static DanhMucGroupAP Filter(string filterExpression)
        {
            try
            {
                DanhMucGroupAP obj = new DanhMucGroupAP();
                obj.DataView = new DataView(DanhMucGroupAP.Instance.DataTable, filterExpression, "", DataViewRowState.CurrentRows);
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static DataTable GetTopAP_RSSMax(int numberOfTop, decimal toaDoX, decimal toaDoY)
        {
            SqlConnection con = new SqlConnection(Common.ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = string.Format("SELECT TOP {0} B.Name, B.MAC, A.RSS \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID \n" +
                                                "WHERE A.ID IN ( \n" +
                                                "	SELECT Signal1ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal2ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal3ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal4ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal5ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal6ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal7ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal8ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal9ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                "	UNION \n" +
                                                "	SELECT Signal10ID FROM GroupAP WHERE ToaDoX = {1} AND ToaDoY = {2} \n" +
                                                ") \n" +
                                                "ORDER BY RSS DESC ",
                                                numberOfTop, toaDoX, toaDoY);
                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static DataTable GetRSSbyName(int id, string MAC)
        {

            SqlConnection con = new SqlConnection(Common.ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = string.Format("SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal1ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal2ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal3ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal4ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal5ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal6ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal7ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal7ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal8ID \n" +
                                                "WHERE  B.MAC = '{0}' \n" +
                                                "UNION  \n" +
                                                "SELECT B.Name, A.RSS, A.[Percent], G.ToaDoX, G.ToaDoY, B.MAC, B.Name \n" +
                                                "FROM Signal A INNER JOIN AP B ON A.APID = B.ID  \n" +
                                                "       INNER JOIN GroupAP G ON A.ID = G.Signal10ID \n" +
                                                "WHERE  B.MAC = '{0}' \n"
                                                , MAC);
                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
        public static DataTable GetAVGRSSbyName(int id, string MAC)
        {

            SqlConnection con = new SqlConnection(Common.ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = string.Format("SELECT X, Y, Name, MAC, ROUND(AVG(CAST(RSS AS FLOAT)), 2) RSS \n" +
                                                "FROM ( \n" +
                                                "SELECT G.X, G.Y, B.Name, B.MAC, A.RSS \n" +
                                                "FROM Signal A INNER JOIN Ap B ON A.AID = B.ID  \n" +
                                                "       INNER JOIN Point G ON A.PID = G.ID  \n" +
                                                "WHERE B.MAC = '{0}' \n" +
                                                ") AS TBL \n" +
                                                "GROUP BY X, Y, Name, MAC \n" +
                                                "order by RSS"
                                                , MAC);
                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
        public static DataTable GetAVGRSSbyName(int id, string MAC, string condition)
        {

            SqlConnection con = new SqlConnection(Common.ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = string.Format("SELECT X, Y, Name, MAC, ROUND(AVG(CAST(RSS AS FLOAT)), 2) RSS \n" +
                                                "FROM ( \n" +
                                                "SELECT G.X, G.Y, B.Name, B.MAC, A.RSS \n" +
                                                "FROM Signal A INNER JOIN Ap B ON A.AID = B.ID  \n" +
                                                "       INNER JOIN Point G ON A.PID = G.ID  \n" +
                                                "WHERE B.MAC = '{0}' AND {1} \n" +
                                                ") AS TBL \n" +
                                                "GROUP BY X, Y, Name, MAC \n" +
                                                "order by RSS"
                                                , MAC, condition);
                SqlDataAdapter da = new SqlDataAdapter();
                DataTable dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }
}
